package com.dao.impl;

import com.dao.SalasDao;
import com.db.ConnectionFactory;
import com.entry.Sales;
import com.util.PageModel;
import java.sql.*;
import java.util.ArrayList;

public class SalasDaoImpl implements SalasDao {
    @Override
    public PageModel<Sales> find(int pageSize, int pageNo,String gid, String sname, float stapirce, float endpirce) {
        String sql = "select gid,sname,scount,stime,price,username from sales";
        if (!("".equals(gid) || gid == null)) {
            sql += " where gid like'%" + gid + "%'";
            if(!("".equals(sname) || sname == null)){
                sql += " and sname like '%"+sname+"%'";
                if(stapirce != 0 && endpirce != 0){
                    sql += " and price between "+stapirce+" and " + endpirce;
                }
            }else if(stapirce != 0 && endpirce != 0){
                sql += " and price between "+stapirce+" and " + endpirce;
            }
        }else if(!("".equals(sname) || sname == null)){
            sql += " where sname like '%"+sname+"%'";
            if(stapirce != 0 && endpirce != 0){
                sql += " and price between "+stapirce+" and " + endpirce;
            }
        }else if(stapirce != 0 && endpirce != 0){
            sql += " where price between "+stapirce+" and " + endpirce;
        }

        ArrayList<Sales> reginfos = new ArrayList<Sales>();
        PageModel<Sales> pm = new PageModel<Sales>(pageSize, pageNo,
                total(sql));
        Connection con = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            pre = con.prepareStatement(sql + " limit " + (pm.getPageNo() - 1)
                    * pm.getPageSize() + "," + pm.getPageSize());
            rs = pre.executeQuery();
            while (rs.next()) {
                Sales sales = new Sales();
                sales.setGid(rs.getString(1));
                sales.setSname(rs.getString(2));
                sales.setScount(rs.getInt(3));
                sales.setStime(rs.getString(4));
                sales.setPrice(rs.getFloat(5));
                sales.setUsername(rs.getString(6));
                reginfos.add(sales);
            }
            pm.setData(reginfos);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pre != null)
                    pre.close();
                if (con != null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return pm;
    }
    @Override
    public int total(String sql) {
        int recordCount = 0;
        String countsql = "select count(*) "
                + sql.substring(sql.indexOf("from"), sql.length());
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            con = ConnectionFactory.getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(countsql);
            if (rs.next()) {
                recordCount = rs.getInt(1);
            }
        }catch (Exception e) {
            e.printStackTrace();
        } finally {
            try{
                if (rs != null){rs.close();}
                if (stmt != null){stmt.close();}
                if (con != null){con.close();}
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return recordCount;
    }
}
